# -*- coding: utf-8 -*-
import codecs
import traceback
import config_json
import logging
import pymssql
import sys
import ejff
SERVER = '10.0.108.107'
USERNAME = 'lims'
PASSWORD = 'QmPz123@me'
DATABASE = 'caiji'
class Server():
    def __init__(self):
        logging.info([config_json.setting["server"],config_json.setting["server"]==""])
        if config_json.setting["server"]=="":
            logging.info("local")
            self.local=True
            self.ejff=ejff.ejff()
        else:
            self.local=False
    def connect(self):
        if sys.version_info.minor>4:
            conn = pymssql.connect(SERVER, USERNAME,PASSWORD, DATABASE,tds_version="7.0")#,charset="CP936")
        else:
            conn = pymssql.connect(SERVER, USERNAME,PASSWORD, DATABASE)#,tds_version="7.0")#,charset="CP936")
        return conn
    def get_syy_depart_local(self,depart):
        r=[]
        return r

    def get_syy_depart(self,depart):
        if self.local:
            return self.get_syy_depart_local()
        r=[]
        conn=self.connect()
        cursor = conn.cursor()
        cursor.execute("select login,name from %s_syy where depart='%s'" % (config_json.setting["server"],depart))
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append((row[0],row[1]))
        cursor.close()
        conn.close()
        return r
    def get_departs(self):
        r=[]
        # conn = pyodbc.connect(self.connectionString)
        conn=self.connect()
        cursor = conn.cursor()
        cursor.execute("select depart from %s_syy group by depart" % config_json.setting["server"])
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append(row[0])
        cursor.close()
        conn.close()
        return r

    def getsyy(self):
        r=[]
        # conn = pyodbc.connect(self.connectionString)
        conn=self.connect()
        cursor = conn.cursor()
        cursor.execute("select login,name,depart,code from %s_syy" % config_json.setting["server"])
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append((row[0],row[1],row[2],row[3]))
        cursor.close()
        conn.close()
        return r
    def getffbh(self):
        r=[]
        # conn = pyodbc.connect(self.connectionString)
        conn=self.connect()
        cursor = conn.cursor()
        cursor.execute("select bh,name,ejff,ejfxjs from %s_ffbh" % config_json.setting["server"])
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append((row[0],row[1],row[2],row[3]))
        cursor.close()
        conn.close()
        return r
    def getsyy_filt_local(self,filt,ejffs):
        r=[]
        for one in config_json.setting["syy"]:
            r.append((one,"","",""))
        return r
    def getsyy_filt(self,filt,ejffs):
        logging.info([self.local])
        if self.local:
            return self.getsyy_filt_local(filt,ejffs)
        if ejffs==None:
            ejffs=[]
        r=[]
        # conn = pyodbc.connect(self.connectionString)
        conn=self.connect()
        cursor = conn.cursor()
        if len(ejffs)>0:
            ejffs=["'"+one+"'" for one in ejffs]
            ejff_list=",".join(ejffs)
            ejff_list="(%s)" % ejff_list
            cmd="select login,name,depart,code from %s_syy where login like '%s' and depart in %s order by login" % (config_json.setting["server"],"%"+filt+"%",ejff_list)
            logging.info(cmd)
            cursor.execute(cmd)
        else:
            cmd="select login,name,depart,code from %s_syy where login like '%s' order by login" % (config_json.setting["server"],"%"+filt+"%")
            logging.info(cmd)
            cursor.execute(cmd)
        while True:
            row = cursor.fetchone()
            if not row:
                break
            depart=row[2]
            r.append((row[0],row[1],depart,row[3]))
        cursor.close()
        conn.close()
        return r
    def getlx_depart_local(self,lx):
        return []
    def getlx_depart(self,lx):
        if self.local:
            return self.getlx_depart_local(lx)
        r=[]
        conn=self.connect()
        cursor = conn.cursor()
        cmd="select depart from %s_depart where lx='%s'" % (config_json.setting["server"],lx)
        logging.info(cmd)
        cursor.execute(cmd)
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append(row[0])
            # logging.info(row[0],dir(row[0]),type(row[0]))
        cursor.close()
        conn.close()
        return r 
    def getlx_ejff_local(self,lx):
        return []#config_json.setting[lx]["ejff"]
    def getlx_ejff(self,lx):
        if self.local:
            return self.getlx_ejff_local(lx)
        r=[]
        conn=self.connect()
        cursor = conn.cursor()
        cmd="select ejff from %s_ejff where lx='%s'" % (config_json.setting["server"],lx)
        logging.info(cmd)
        cursor.execute(cmd)
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append(row[0])
            # logging.info(row[0],dir(row[0]),type(row[0]))
        cursor.close()
        conn.close()
        return r     
    def getlx_ffbh_filt_local(self,filt,ejffs):
        r=[]
        for  one in self.ejff:
            # logging.warning(one)
            # logging.warning([filt,ejffs])
            if ( (len(ejffs)==0) or (one[2] in ejffs)) and ( filt=="" or (filt in one[0])):
                r.append(one)
        return r
    def getffbh_filt(self,filt,ejffs):
        if self.local:
            return self.getlx_ffbh_filt_local(filt,ejffs)
        logging.info(ejffs)
        r=[]
        # conn = pyodbc.connect(self.connectionString)
        conn=self.connect()
        cursor = conn.cursor()
        if len(ejffs)>0:
            ejffs=["'"+one+"'" for one in ejffs]
            ejff_list=",".join(ejffs)
            ejff_list="(%s)" % ejff_list
            cmd="select bh,name,ejff,ejfxjs from %s_ffbh where bh like '%s' and ejff in %s" % (config_json.setting["server"],"%"+filt+"%",ejff_list)
            # cmd="select bh,name,ejff,ejfxjs from %s_ffbh where bh like '%s' and %s order by bh" % (config_json.setting["server"],"%"+filt+"%",ejffs)
            logging.info(cmd)
            cursor.execute(cmd)
        else:
            cmd="select bh,name,ejff,ejfxjs from %s_ffbh where bh like '%s' order by bh" % (config_json.setting["server"],"%"+filt+"%")
            logging.info(cmd)
            cursor.execute(cmd)
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append((row[0],row[1],row[2],row[3]))
        cursor.close()
        conn.close()
        return r        
    def getversion(self):
        if self.local:
            return []
        r=[]
        conn=self.connect()
        cursor = conn.cursor()
        cursor.execute("select value from version")
        while True:
            row = cursor.fetchone()
            if not row:
                break
            r.append((row[0]))
        cursor.close()
        conn.close()
        return r
def test():
    db=Server()
    r=db.getversion()
    r=db.getsyy()
    print(r)

if __name__=="__main__":    
    # updateDb()    
    # get_data()
    # s1()
    test()
